package jp.ac.u_tokyo.dch.dao.schema;

import java.util.ArrayList;
import java.util.List;

import jp.ac.u_tokyo.dch.core.db.CustomPreparedStatement;
import jp.ac.u_tokyo.dch.core.db.CustomResultSet;
import jp.ac.u_tokyo.dch.core.db.DBConnection;
import jp.ac.u_tokyo.dch.core.exceptions.DBException;
import jp.ac.u_tokyo.dch.model.db.DataSchemaDetail;
import jp.ac.u_tokyo.dch.model.db.FieldMaster;
import jp.ac.u_tokyo.dch.util.common.CommonDBConnection;
/**
* @version 1.1
*/
public class DataSchemaDetailDao {

	/**
	 * DBコネクションオブジェクト
	 */
	private DBConnection dbconn = null;

	/**
	 * 生成者
	 * @param DBコネクションオブジェクト
	 */
	public DataSchemaDetailDao(DBConnection dbconn){
		this.dbconn = dbconn;
	}


	/**
	 * スキーマ詳細項目を取得（データ表示詳細テーブルにない情報だけ）
	 * @param keyschemaid キースキーマID
	 * @param schemaid スキーマID
	 * @param customsign カスタムサイン（０：カスタムではない、１：カスタム）
	 * @return スキーマ詳細リスト
	 * @throws DBException 例外
	 */
	public List<DataSchemaDetail> selectListMinusDisplay(String keyschemaid, String schemaid, String customsign) throws DBException {

		List<DataSchemaDetail> list = new ArrayList<DataSchemaDetail>();
		CustomPreparedStatement ps = null;
		CustomResultSet rs = null;
		StringBuilder query = new StringBuilder();
		try{
			query.append("SELECT * FROM data_schema_detail INNER JOIN field_master on data_schema_detail.fieldkey = field_master.fieldkey ");
			query.append("WHERE schemaid = ? and customsign = ? and display = '0' ");
			query.append("and NOT EXISTS (select * from data_displays INNER JOIN data_display_detail on data_displays.id =  data_display_detail.displaysid");
			query.append(" where keyschemaid = ? and schemaid = data_schema_detail.schemaid and fieldkey = data_schema_detail.fieldkey)");

			ps = dbconn.prepareStatement(query.toString());

			ps.setInt(1, Integer.valueOf(schemaid));
			ps.setString(2, customsign);
			ps.setString(3, keyschemaid);

			rs = ps.executeQuery();
			while (rs.next()){

				DataSchemaDetail dataSchemaDetail = new DataSchemaDetail();
				dataSchemaDetail.setSchemaid(rs.getString("schemaid"));
				dataSchemaDetail.setFieldKey(rs.getString("fieldkey"));
				dataSchemaDetail.setInputType(rs.getString("inputtype"));
				dataSchemaDetail.setOptionKbn(rs.getString("optionkbn"));
				dataSchemaDetail.setRequired(rs.getString("required"));
				dataSchemaDetail.setDisplay(rs.getString("display"));
				dataSchemaDetail.setTmSign(convertDbToCheckBox(rs.getString("tmsign")));
				dataSchemaDetail.setHissuSelectSign(rs.getString("hissusign"));
				dataSchemaDetail.setPortaSign(convertDbToCheckBox(rs.getString("portasearch")));
				dataSchemaDetail.setContent(rs.getString("content"));
				dataSchemaDetail.setCustomSign(rs.getString("customsign"));
				dataSchemaDetail.setFieldDisplayOrder(rs.getString("displayorder"));
				dataSchemaDetail.setFieldDisplayname(rs.getString("displayname"));
				dataSchemaDetail.setFieldItemName(rs.getString("itemname"));
				dataSchemaDetail.setSort(rs.getString("sort"));

				list.add(dataSchemaDetail);
			}
		}catch(DBException dbe){
			throw dbe;
		}finally{
			CommonDBConnection.closeResource(ps, rs);
		}
		return list;
	}

	/**
	 * カスタムデータスキーマリストを取得する
	 * @return カスタムデータスキーマリスト
	 * @throws DBException 例外
	 */
	public List<DataSchemaDetail> selectCustomList() throws DBException {

		List<DataSchemaDetail> list = new ArrayList<DataSchemaDetail>();
		CustomPreparedStatement ps = null;
		CustomResultSet rs = null;
		StringBuilder query = new StringBuilder();
		try{
			query.append("SELECT * FROM data_schema_detail ");
			query.append(" INNER JOIN field_master on data_schema_detail.fieldkey = field_master.fieldkey ");
			query.append(" INNER JOIN data_schema on data_schema_detail.schemaid = data_schema.schemaid where customsign = '1' order by data_schema.sort ");

			ps = dbconn.prepareStatement(query.toString());

			rs = ps.executeQuery();
			while (rs.next()){

				DataSchemaDetail dataSchemaDetail = new DataSchemaDetail();
				dataSchemaDetail.setSchemaid(rs.getString("schemaid"));
				dataSchemaDetail.setFieldKey(rs.getString("fieldkey"));
				dataSchemaDetail.setInputType(rs.getString("inputtype"));
				dataSchemaDetail.setOptionKbn(rs.getString("optionkbn"));
				dataSchemaDetail.setRequired(rs.getString("required"));
				dataSchemaDetail.setDisplay(rs.getString("display"));
				dataSchemaDetail.setTmSign(convertDbToCheckBox(rs.getString("tmsign")));
				dataSchemaDetail.setHissuSelectSign(rs.getString("hissusign"));
				dataSchemaDetail.setPortaSign(convertDbToCheckBox(rs.getString("portasearch")));
				dataSchemaDetail.setContent(rs.getString("content"));
				dataSchemaDetail.setCustomSign(rs.getString("customsign"));
				dataSchemaDetail.setFieldDisplayOrder(rs.getString("displayorder"));
				dataSchemaDetail.setFieldDisplayname(rs.getString("displayname"));
				dataSchemaDetail.setFieldItemName(rs.getString("itemname"));

				list.add(dataSchemaDetail);
			}
		}catch(DBException dbe){
			throw dbe;
		}finally{
			CommonDBConnection.closeResource(ps, rs);
		}
		return list;
	}

	public List<FieldMaster> selectCustomField() throws DBException {

		List<FieldMaster> list = new ArrayList<FieldMaster>();
		CustomPreparedStatement ps = null;
		CustomResultSet rs = null;
		try{
			String query = "SELECT * FROM data_schema_detail WHERE customsign = 1 order by fieldkey";

			ps = dbconn.prepareStatement(query);


			rs = ps.executeQuery();
			while (rs.next()){

				FieldMaster fieldMaster = new FieldMaster();
				fieldMaster.setFieldkey(rs.getString("fieldkey"));
				fieldMaster.setDisplayName(rs.getString("displayname"));
				list.add(fieldMaster);
			}
		}catch(DBException dbe){
			throw dbe;
		}finally{
			CommonDBConnection.closeResource(ps, rs);
		}
		return list;
	}

	/**
	 * データスキーマリストを取得する
	 * @param schemaid スキーマID
	 * @param customsign カスタムサイン（０：カスタムではない、１：カスタム）
	 * @return データスキーマリスト
	 * @throws DBException 例外
	 */
	public List<DataSchemaDetail> selectList(String schemaid, String customsign) throws DBException {

		List<DataSchemaDetail> list = new ArrayList<DataSchemaDetail>();
		CustomPreparedStatement ps = null;
		CustomResultSet rs = null;
		try{
			String query = "SELECT * FROM data_schema_detail INNER JOIN field_master on data_schema_detail.fieldkey = field_master.fieldkey WHERE schemaid = ? and customsign = ? ";

			ps = dbconn.prepareStatement(query);

			ps.setInt(1, Integer.valueOf(schemaid));
			ps.setString(2, customsign);

			rs = ps.executeQuery();
			while (rs.next()){

				DataSchemaDetail dataSchemaDetail = new DataSchemaDetail();
				dataSchemaDetail.setSchemaid(rs.getString("schemaid"));
				dataSchemaDetail.setFieldKey(rs.getString("fieldkey"));
				dataSchemaDetail.setInputType(rs.getString("inputtype"));
				dataSchemaDetail.setOptionKbn(rs.getString("optionkbn"));
				dataSchemaDetail.setRequired(rs.getString("required"));
				dataSchemaDetail.setDisplay(rs.getString("display"));
				dataSchemaDetail.setTmSign(convertDbToCheckBox(rs.getString("tmsign")));
				dataSchemaDetail.setHissuSelectSign(rs.getString("hissusign"));
				dataSchemaDetail.setPortaSign(convertDbToCheckBox(rs.getString("portasearch")));
				dataSchemaDetail.setContent(rs.getString("content"));
				dataSchemaDetail.setCustomSign(rs.getString("customsign"));
				dataSchemaDetail.setFieldDisplayOrder(rs.getString("displayorder"));
				dataSchemaDetail.setFieldDisplayname(rs.getString("displayname"));
				dataSchemaDetail.setFieldItemName(rs.getString("itemname"));

				list.add(dataSchemaDetail);
			}
		}catch(DBException dbe){
			throw dbe;
		}finally{
			CommonDBConnection.closeResource(ps, rs);
		}
		return list;
	}

	/**
	 * データスキーマリストを取得する
	 * @param schemaid スキーマID
	 * @return データスキーマリスト
	 * @throws DBException 例外
	 */
	public List<DataSchemaDetail> selectList(String schemaid) throws DBException {

		List<DataSchemaDetail> list = new ArrayList<DataSchemaDetail>();
		CustomPreparedStatement ps = null;
		CustomResultSet rs = null;
		try{
			String query = "SELECT * FROM data_schema_detail INNER JOIN field_master on data_schema_detail.fieldkey = field_master.fieldkey WHERE schemaid = ? ";

			ps = dbconn.prepareStatement(query);

			ps.setInt(1, Integer.valueOf(schemaid));

			rs = ps.executeQuery();
			while (rs.next()){

				DataSchemaDetail dataSchemaDetail = new DataSchemaDetail();
				dataSchemaDetail.setSchemaid(rs.getString("schemaid"));
				dataSchemaDetail.setFieldKey(rs.getString("fieldkey"));
				dataSchemaDetail.setInputType(rs.getString("inputtype"));
				dataSchemaDetail.setOptionKbn(rs.getString("optionkbn"));
				dataSchemaDetail.setRequired(rs.getString("required"));
				dataSchemaDetail.setDisplay(rs.getString("display"));
				dataSchemaDetail.setTmSign(convertDbToCheckBox(rs.getString("tmsign")));
				dataSchemaDetail.setHissuSelectSign(rs.getString("hissusign"));
				dataSchemaDetail.setPortaSign(convertDbToCheckBox(rs.getString("portasearch")));
				dataSchemaDetail.setContent(rs.getString("content"));
				dataSchemaDetail.setCustomSign(rs.getString("customsign"));
				dataSchemaDetail.setFieldDisplayOrder(rs.getString("displayorder"));
				dataSchemaDetail.setFieldDisplayname(rs.getString("displayname"));
				dataSchemaDetail.setFieldItemName(rs.getString("itemname"));

				list.add(dataSchemaDetail);
			}
		}catch(DBException dbe){
			throw dbe;
		}finally{
			CommonDBConnection.closeResource(ps, rs);
		}
		return list;
	}

	/**
	 * データスキーマ詳細を取得する
	 * @param id データスキーマID
	 * @param fieldKey 項目キー
	 * @return データスキーマ詳細情報
	 * @throws DBException 例外
	 */
	public DataSchemaDetail selectObject(String id, String fieldKey) throws DBException {

		DataSchemaDetail dataSchemaDetail = null;
		CustomPreparedStatement ps = null;
		CustomResultSet rs = null;
		try{
			String query = "SELECT * FROM data_schema_detail INNER JOIN field_master on data_schema_detail.fieldkey = field_master.fieldkey WHERE schemaid = ? and fieldkey = ?";

			ps = dbconn.prepareStatement(query);
			ps.setString(1, id);

			rs = ps.executeQuery();

			if (rs.next()){

				dataSchemaDetail = new DataSchemaDetail();

				dataSchemaDetail.setSchemaid(rs.getString("schemaid"));
				dataSchemaDetail.setFieldKey(rs.getString("fieldkey"));
				dataSchemaDetail.setInputType(rs.getString("inputtype"));
				dataSchemaDetail.setOptionKbn(rs.getString("optionkbn"));
				dataSchemaDetail.setRequired(rs.getString("required"));
				dataSchemaDetail.setDisplay(rs.getString("display"));
				dataSchemaDetail.setTmSign(convertDbToCheckBox(rs.getString("tmsign")));
				dataSchemaDetail.setHissuSelectSign(rs.getString("hissusign"));
				dataSchemaDetail.setPortaSign(convertDbToCheckBox(rs.getString("portasearch")));
				dataSchemaDetail.setContent(rs.getString("content"));
				dataSchemaDetail.setCustomSign(rs.getString("customsign"));
				dataSchemaDetail.setFieldDisplayOrder(rs.getString("displayorder"));
				dataSchemaDetail.setFieldDisplayname(rs.getString("displayname"));
				dataSchemaDetail.setFieldItemName(rs.getString("itemname"));
			}
		}catch(DBException dbe){
			throw dbe;
		}finally{
			CommonDBConnection.closeResource(ps, rs);
		}
		return dataSchemaDetail;
	}

	/**
	 * データスキーマ詳細を挿入する
	 * @param list データスキーマリスト
	 * @param schemaId スキーマID
	 * @return 挿入件数情報
	 * @throws DBException 例外
	 */
	public int[] insertAll(List<DataSchemaDetail> list, int schemaId)  throws DBException {

		CustomPreparedStatement ps = null;
		int result[] = null;
		try{
			String query = "INSERT INTO data_schema_detail (schemaid,fieldkey,inputtype,optionkbn,required,display,tmsign, hissusign,portasearch,content,customsign)" +
					" VALUES (?,?,?,?,?,?,?,?,?,?,?)";

			ps = dbconn.prepareStatement(query);

			for (DataSchemaDetail detail : list) {

				int index = 1;
				ps.setInt(index++, schemaId);
				ps.setString(index++, detail.getFieldKey());
				ps.setString(index++, detail.getInputType());
				ps.setString(index++, detail.getOptionKbn());
				ps.setString(index++, detail.getRequired());
				ps.setString(index++, detail.getDisplay());
				ps.setString(index++, convertCheckBoxToDb(detail.getTmSign()));
				ps.setString(index++, detail.getHissuSelectSign());
				ps.setString(index++, convertCheckBoxToDb(detail.getPortaSign()));
				ps.setString(index++, detail.getContent());
				ps.setString(index++, detail.getCustomSign());
				ps.addBatch();

			}

			result = ps.executeBatch();
		}catch(DBException dbe){
			throw dbe;
		}finally{
			CommonDBConnection.closeResource(ps);
		}
		return result;

	}


	/**
	 * データスキーマ詳細を挿入する
	 * @param dataSchemaDetail データスキーマ詳細情報
	 * @return 挿入件数
	 * @throws DBException 例外
	 */
	public int insert(DataSchemaDetail dataSchemaDetail)  throws DBException {

		CustomPreparedStatement ps = null;
		int result = 0;
		int index = 1;
		try{
			String query = "INSERT INTO data_schema_detail (schemaid,fieldkey,inputtype,optionkbn,required,display,tmsign, hissusign,portasearch,content,customsign)" +
					" VALUES (?,?,?,?,?,?,?,?,?,?,?)";

			ps = dbconn.prepareStatement(query);
			ps.setInt(index++, Integer.valueOf(dataSchemaDetail.getSchemaid()));
			ps.setString(index++, dataSchemaDetail.getFieldKey());
			ps.setString(index++, dataSchemaDetail.getInputType());
			ps.setString(index++, dataSchemaDetail.getOptionKbn());
			ps.setString(index++, dataSchemaDetail.getRequired());
			ps.setString(index++, dataSchemaDetail.getDisplay());
			ps.setString(index++, convertCheckBoxToDb(dataSchemaDetail.getTmSign()));
			ps.setString(index++, convertCheckBoxToDb(dataSchemaDetail.getPortaSign()));
			ps.setString(index++, dataSchemaDetail.getHissuSelectSign());
			ps.setString(index++, dataSchemaDetail.getContent());
			ps.setString(index++, dataSchemaDetail.getCustomSign());


			result = ps.executeUpdate();
		}catch(DBException dbe){
			throw dbe;
		}finally{
			CommonDBConnection.closeResource(ps);
		}
		return result;
	}

	/**
	 * 削除する
	 * @param schemaId スキーマID
	 * @return 削除件数
	 * @throws DBException 例外
	 */
	public int deleteAll(Integer schemaId)  throws DBException {

		CustomPreparedStatement ps = null;
		int result = 0;
		try{
			String query = "DELETE FROM data_schema_detail WHERE schemaid = ?";

			ps = dbconn.prepareStatement(query);
			ps.setInt(1, schemaId);
			result = ps.executeUpdate();
		}catch(DBException dbe){
			throw dbe;
		}finally{
			CommonDBConnection.closeResource(ps);
		}
		return result;
	}

	/**
	 * カスタム情報を削除する
	 * @param schemaId スキーマID
	 * @return 削除件数
	 * @throws DBException 例外
	 */
	public int deleteCustom(Integer schemaId)  throws DBException {

		CustomPreparedStatement ps = null;
		int result = 0;
		try{
			String query = "DELETE FROM data_schema_detail WHERE schemaid = ? and customsign = '1'";

			ps = dbconn.prepareStatement(query);
			ps.setInt(1, schemaId);
			result = ps.executeUpdate();
		}catch(DBException dbe){
			throw dbe;
		}finally{
			CommonDBConnection.closeResource(ps);
		}
		return result;
	}

	/**
	 * 特定項目のスキーマ詳細情報を削除する
	 * @param schemaId スキーマID
	 * @param fieldkey 項目キー
	 * @return 削除件数
	 * @throws DBException 例外
	 */
	public int delete(Integer schemaId, String fieldkey)  throws DBException {

		CustomPreparedStatement ps = null;
		int result = 0;
		try{
			String query = "DELETE data_schema_detail WHERE schemaid = ? and fieldkey = ?";

			ps = dbconn.prepareStatement(query);
			ps.setInt(1, schemaId);
			ps.setString(2, fieldkey);
			result = ps.executeUpdate();
		}catch(DBException dbe){
			throw dbe;
		}finally{
			CommonDBConnection.closeResource(ps);
		}
		return result;
	}

	/**
	 * 更新する
	 * @param dataSchemaDetail データスキーマ詳細
	 * @return 更新件数
	 * @throws DBException 例外
	 */
	public int update(DataSchemaDetail dataSchemaDetail) throws DBException {

		CustomPreparedStatement ps = null;
		int result = 0;
		int index = 1;
		try{
			String query = "UPDATE data_schema_detail SET inputtype = ?, optionkbn = ?, required = ?, display = ?, tmsign = ?, " +
					"hissusign = ?, portasearch = ?, content = ?, customsign = ? WHERE schemaid = ?";

			ps = dbconn.prepareStatement(query);
			ps.setInt(index++, Integer.valueOf(dataSchemaDetail.getSchemaid()));
			ps.setString(index++, dataSchemaDetail.getFieldKey());
			ps.setString(index++, dataSchemaDetail.getInputType());
			ps.setString(index++, dataSchemaDetail.getOptionKbn());
			ps.setString(index++, dataSchemaDetail.getRequired());
			ps.setString(index++, dataSchemaDetail.getDisplay());
			ps.setString(index++, dataSchemaDetail.getTmSign());
			ps.setString(index++, dataSchemaDetail.getHissuSelectSign());
			ps.setString(index++, dataSchemaDetail.getPortaSign());
			ps.setString(index++, dataSchemaDetail.getContent());
			ps.setString(index++, dataSchemaDetail.getCustomSign());

			result = ps.executeUpdate();
		}catch(DBException dbe){
			throw dbe;
		}finally{
			CommonDBConnection.closeResource(ps);
		}
		return result;
	}

	/**
	 * データスキーマ詳細リストを更新する
	 * @param list データスキーマ詳細リスト
	 * @return 更新件数情報
	 * @throws DBException 例外
	 */
	public int[] updateAll(List<DataSchemaDetail> list) throws DBException {

		CustomPreparedStatement ps = null;
		int result[] = null;
		try{
			String query = "UPDATE data_schema_detail SET inputtype = ?, optionkbn = ?, required = ?, display = ?, tmsign = ?, " +
			"hissusign = ?, portasearch = ?, content = ?, customsign = ? WHERE schemaid = ? and fieldkey = ?";

			ps = dbconn.prepareStatement(query);

			for (DataSchemaDetail dataSchemaDetail : list) {

				int index = 1;

				ps.setString(index++, dataSchemaDetail.getInputType());
				ps.setString(index++, dataSchemaDetail.getOptionKbn());
				ps.setString(index++, dataSchemaDetail.getRequired());
				ps.setString(index++, dataSchemaDetail.getDisplay());
				ps.setString(index++, convertCheckBoxToDb(dataSchemaDetail.getTmSign()));
				ps.setString(index++, dataSchemaDetail.getHissuSelectSign());
				ps.setString(index++, convertCheckBoxToDb(dataSchemaDetail.getPortaSign()));
				ps.setString(index++, dataSchemaDetail.getContent());
				ps.setString(index++, dataSchemaDetail.getCustomSign());
				ps.setInt(index++, Integer.valueOf(dataSchemaDetail.getSchemaid()));
				ps.setString(index++, dataSchemaDetail.getFieldKey());
				ps.addBatch();
			}

			result = ps.executeBatch();
		}catch(DBException dbe){
			throw dbe;
		}finally{
			CommonDBConnection.closeResource(ps);
		}
		return result;
	}

	/**
	 * 画面のPORTAサインをDB値に変換
	 * @param true、false
	 * @return true : 1、false : 0
	 */
	private String convertCheckBoxToDb(String viewValue) {

		if ("true".equals(viewValue)) {
			return "1";
		} else {
			return "0";
		}

	}

	/**
	 * DB値を画面に表示する値に変換
	 * @param dbValue 0、1
	 * @return 0：false、1:true
	 */
	private String convertDbToCheckBox(String dbValue) {

		if ("1".equals(dbValue)) {
			return "true";
		} else {
			return "false";
		}
	}
}
